﻿Imports SS
Imports SS.Databases
Imports SS.Databases.Connecter
Imports SS.ApplicationBlock

Public Class Package

    Dim connOra As DBOracle
    Public ReadOnly Property CurrentConnection As DBOracle
        Get
            Return connOra
        End Get
    End Property

    Public Sub New()

    End Sub

    Public Sub New(ByVal Transaction As Boolean)
        connOra = New DBOracle(Common.Utility.ConnectString, Transaction)
    End Sub

    Private Shared Function Excute(ByVal sql As String) As DataTable
        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Return connOraSelect.GetDataTable(sql, "Data")
        End Using
    End Function

    Public Sub Commit()
        connOra.Commit()
        connOra.Close()
    End Sub

    Public Sub RollBack()
        connOra.Rollback()
        connOra.Close()
    End Sub

    Public Shared Function AllPackage() As List(Of Entity.PackageItem)

        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Dim dt As DataTable = connOraSelect.GetDataTable("SELECT * FROM " & Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE", "RNWM_CONFIG_RECONCILE")
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                Dim objReturn As List(Of Entity.PackageItem)
                objReturn = New List(Of Entity.PackageItem)
                objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.PackageItem)(dt)
                Return objReturn
            Else
                Return Nothing
            End If
        End Using

    End Function

    Public Shared Function GetPackage(ByVal info As Entity.PackageItem) As Data.DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT ")
        sb.AppendLine("PACKAGE_ID, ")
        sb.AppendLine("PACKAGE_RV, ")
        sb.AppendLine("PACKAGE_AMOUNT, ")
        sb.AppendLine("PACKAGE_DESC, ")
        sb.AppendLine("RECORD_STATUS, ")
        sb.AppendLine("DECODE(RECORD_STATUS,'A','ACTIVE','I','INACTIVE') RECORD_STATUS_DESC, ")
        sb.AppendLine("CREATE_USER_ID, ")
        sb.AppendLine("CREATE_DATE, ")
        sb.AppendLine("UPD_USER_ID ")
        sb.AppendLine("UPD_DATE ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE")

        sb.AppendLine("WHERE 1=1 ")

        If Not String.IsNullOrEmpty(info.package_id) AndAlso info.package_id <> "" Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.PACKAGE_ID LIKE '%" & info.package_id & "%'")
        End If

        If Not IsNothing(info.package_rv) Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.PACKAGE_RV LIKE '%" & info.package_rv & "%'")
        End If

        If Not IsNothing(info.package_amount) Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.PACKAGE_AMOUNT LIKE '%" & info.package_amount & "%'")
        End If

        If Not IsNothing(info.package_desc) Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.PACKAGE_DESC LIKE '%" & info.package_desc & "%'")
        End If

        If Not String.IsNullOrEmpty(info.record_status) AndAlso info.record_status <> "" Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.RECORD_STATUS = '" & info.record_status & "'")
        End If

        sb.AppendLine("ORDER BY PACKAGE_ID ")

        Return Excute(sb.ToString)
    End Function

    Public Shared Function GetPackageById(ByVal info As String) As Entity.PackageItem
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE")
        sb.AppendLine("WHERE 1=1 ")

        If Not IsNothing(info) Then
            sb.AppendLine("AND RNWM_CONFIG_RECONCILE.PACKAGE_ID = '" & info & "'")
        End If
        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        Dim oInfo As Entity.PackageItem = New Entity.PackageItem
        If Not IsDBNull(dt.Rows(0)("PACKAGE_ID")) Then oInfo.package_id = dt.Rows(0)("PACKAGE_ID")
        If Not IsDBNull(dt.Rows(0)("PACKAGE_RV")) Then oInfo.package_rv = dt.Rows(0)("PACKAGE_RV")
        If Not IsDBNull(dt.Rows(0)("PACKAGE_AMOUNT")) Then oInfo.package_amount = dt.Rows(0)("PACKAGE_AMOUNT")
        If Not IsDBNull(dt.Rows(0)("PACKAGE_DESC")) Then oInfo.package_desc = dt.Rows(0)("PACKAGE_DESC")
        If Not IsDBNull(dt.Rows(0)("RECORD_STATUS")) Then oInfo.record_status = dt.Rows(0)("RECORD_STATUS")
        If Not IsDBNull(dt.Rows(0)("CREATE_USER_ID")) Then oInfo.create_user_id = dt.Rows(0)("CREATE_USER_ID")
        If Not IsDBNull(dt.Rows(0)("CREATE_DATE")) Then oInfo.create_date = Convert.ToDateTime(dt.Rows(0)("CREATE_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        If Not IsDBNull(dt.Rows(0)("UPD_USER_ID")) Then oInfo.upd_user_id = dt.Rows(0)("UPD_USER_ID")
        If Not IsDBNull(dt.Rows(0)("UPD_DATE")) Then oInfo.upd_date = Convert.ToDateTime(dt.Rows(0)("UPD_DATE")).ToString("dd/MM/yyyy", Common.Utility.CulInfo)
        Return oInfo
    End Function

    Public Function Insert(ByVal info As Entity.PackageItem) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Update(ByVal info As Entity.PackageItem) As Boolean
        Dim sb As New Text.StringBuilder
        sb.AppendLine("Update " & Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE SET")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_RV = " & info.package_rv & " ,")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_AMOUNT = " & info.package_amount & " ,")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.PACKAGE_DESC = '" & info.package_desc & "' ,")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.RECORD_STATUS ='" & info.record_status & "',")
        sb.AppendLine("UPD_USER_ID='" & info.upd_user_id & "',")
        sb.AppendLine("RNWM_CONFIG_RECONCILE.UPD_DATE = sysdate")
        sb.AppendLine("WHERE RNWM_CONFIG_RECONCILE.PACKAGE_ID = '" & info.package_id & "'")
        Try
            If connOra.ExecuteNonQuery(sb.ToString) = 1 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Common.LogError.AddLogException(ex)
            Return False
        End Try
    End Function

    Public Function InsertHistory(ByVal info As Entity.PackageItem) As Boolean
        info.history_id = GetNewHistoryID()
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE_H")
        If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function GetNewHistoryID() As Decimal
        Dim RVID As Integer = 0
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT * FROM( ")
        sb.AppendLine("Select HISTORY_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_CONFIG_RECONCILE_H")
        sb.AppendLine("ORDER BY HISTORY_ID DESC ")
        sb.AppendLine(")WHERE ROWNUM = 1 ")

        Dim dt As DataTable = New DataTable
        dt = Excute(sb.ToString)
        If dt.Rows.Count > 0 Then
            RVID = Convert.ToDecimal(dt.Rows(0)("HISTORY_ID")) + 1
        Else
            RVID = 1
        End If
        Return RVID
    End Function

End Class
